package com.bank.dao;

import com.bank.entity.Transaction;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Date;

public class TransactionDAO {
    /**
     * 多条件组合查询交易记录
     * @param cardId 卡号（支持模糊查询）
     * @param tradeType 交易类型（精确匹配）
     * @param startDate 开始日期（包含）
     * @param endDate 结束日期（包含）
     * @param remark 备注（支持模糊查询）
     * @param tradeId 交易ID（精确匹配）
     * @return 符合条件的交易记录列表
     */
    public List<Transaction> queryTransactionsByCriteria(
            String cardId, String tradeType, Date startDate, Date endDate,
            String remark, Integer tradeId) throws SQLException {

        List<Transaction> list = new ArrayList<>();
        StringBuilder sql = new StringBuilder("SELECT * FROM tradeInfo WHERE 1=1");
        List<Object> params = new ArrayList<>();

        // 动态构建查询条件
        if (cardId != null && !cardId.isEmpty()) {
            sql.append(" AND cardID LIKE ?");
            params.add("%" + cardId + "%");
        }

        if (tradeType != null && !tradeType.isEmpty()) {
            sql.append(" AND tradeType = ?");
            params.add(tradeType);
        }

        if (startDate != null) {
            sql.append(" AND tradeDate >= ?");
            params.add(new Timestamp(startDate.getTime()));
        }

        if (endDate != null) {
            sql.append(" AND tradeDate <= ?");
            params.add(new Timestamp(endDate.getTime()));
        }

        if (remark != null && !remark.isEmpty()) {
            sql.append(" AND remark LIKE ?");
            params.add("%" + remark + "%");
        }

        if (tradeId != null) {
            sql.append(" AND tradeID = ?");
            params.add(tradeId);
        }

        sql.append(" ORDER BY tradeDate DESC");

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql.toString())) {

            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                ps.setObject(i + 1, params.get(i));
            }

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(mapResultSetToTransaction(rs));
                }
            }
        }
        return list;
    }

    /**
     * 添加交易记录
     */
    public void addTransaction(Transaction transaction) throws SQLException {
        String sql = "INSERT INTO tradeInfo (tradeDate, tradeType, cardID, tradeMoney, remark) VALUES (?, ?, ?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setTimestamp(1, new Timestamp(transaction.getTradeDate().getTime()));
            ps.setString(2, transaction.getTradeType());
            ps.setString(3, transaction.getCardID());
            ps.setDouble(4, transaction.getTradeMoney());
            ps.setString(5, transaction.getRemark());
            ps.executeUpdate();
        }
    }

    /**
     * 执行转账操作（通过存储过程实现）
     * @param fromCard 转出卡号
     * @param toCard 转入卡号
     * @param amount 转账金额
     * @return 包含结果代码和消息的字符串数组 [结果码, 消息]
     * @throws SQLException 如果数据库操作发生错误
     */
    public String[] transferFunds(String fromCard, String toCard, double amount) throws SQLException {
        String[] result = new String[2];
        String sql = "{call proc_transfer1(?, ?, ?, ?, ?)}";  // 调用存储过程

        try (Connection conn = DBUtil.getConnection();
             CallableStatement cs = conn.prepareCall(sql)) {

            // 设置输入参数
            cs.setString(1, fromCard);
            cs.setString(2, toCard);
            cs.setDouble(3, amount);

            // 注册输出参数
            cs.registerOutParameter(4, Types.INTEGER);   // 结果码
            cs.registerOutParameter(5, Types.VARCHAR);   // 消息

            // 执行存储过程
            cs.execute();

            // 获取结果
            result[0] = String.valueOf(cs.getInt(4));    // 结果码转字符串
            result[1] = cs.getString(5);                 // 消息文本
        }
        return result;
    }

    /**
     * 查询所有交易记录
     */
    public List<Transaction> getAllTransactions() throws SQLException {
        List<Transaction> list = new ArrayList<>();
        try (Connection conn = DBUtil.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM tradeInfo")) {
            while (rs.next()) {
                list.add(mapResultSetToTransaction(rs));
            }
        }
        return list;
    }

    /**
     * 删除指定交易记录并更新银行卡余额
     */
    public boolean deleteTransaction(int tradeID) throws SQLException {
        Connection conn = null;
        try {
            conn = DBUtil.getConnection();
            conn.setAutoCommit(false); // 开始事务

            // 1. 获取交易记录详情
            String selectSql = "SELECT * FROM tradeInfo WHERE tradeID = ? FOR UPDATE";
            Transaction transaction = null;
            try (PreparedStatement selectPs = conn.prepareStatement(selectSql)) {
                selectPs.setInt(1, tradeID);
                try (ResultSet rs = selectPs.executeQuery()) {
                    if (rs.next()) {
                        transaction = mapResultSetToTransaction(rs);
                    } else {
                        throw new SQLException("交易记录不存在");
                    }
                }
            }

            // 2. 根据交易类型反向调整银行卡余额
            String tradeType = transaction.getTradeType();
            String updateSql = "";

            if ("存款".equals(tradeType) || "存入".equals(tradeType)) {
                updateSql = "UPDATE cardInfo SET balance = balance - ? WHERE cardID = ?";
            } else if ("取款".equals(tradeType) || "支取".equals(tradeType)) {
                updateSql = "UPDATE cardInfo SET balance = balance + ? WHERE cardID = ?";
            } else {
                throw new SQLException("不支持此交易类型的删除操作");
            }

            try (PreparedStatement updatePs = conn.prepareStatement(updateSql)) {
                updatePs.setDouble(1, transaction.getTradeMoney());
                updatePs.setString(2, transaction.getCardID());
                int rows = updatePs.executeUpdate();
                if (rows == 0) {
                    throw new SQLException("更新银行卡余额失败");
                }
            }

            // 3. 物理删除交易记录
            String deleteSql = "DELETE FROM tradeInfo WHERE tradeID = ?";
            try (PreparedStatement deletePs = conn.prepareStatement(deleteSql)) {
                deletePs.setInt(1, tradeID);
                deletePs.executeUpdate();
            }

            conn.commit(); // 提交事务
            return true;
        } catch (SQLException e) {
            if (conn != null) {
                conn.rollback(); // 回滚事务
            }
            throw e;
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true); // 恢复自动提交
                } finally {
                    conn.close();
                }
            }
        }
    }

    /**
     * 更新交易记录备注
     */
    public boolean updateTransactionRemark(int tradeID, String newRemark) throws SQLException {
        String sql = "UPDATE tradeInfo SET remark = ? WHERE tradeID = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, newRemark);
            ps.setInt(2, tradeID);
            return ps.executeUpdate() > 0;
        }
    }

    /**
     * 按卡号查询交易记录
     */
    public List<Transaction> getTransactionsByCardID(String cardID) throws SQLException {
        List<Transaction> list = new ArrayList<>();
        String sql = "SELECT * FROM tradeInfo WHERE cardID = ? ORDER BY tradeDate DESC";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, cardID);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(mapResultSetToTransaction(rs));
                }
            }
        }
        return list;
    }

    /**
     * 按备注模糊查询交易记录
     */
    public List<Transaction> getTransactionsByRemarkLike(String keyword) throws SQLException {
        List<Transaction> list = new ArrayList<>();
        String sql = "SELECT * FROM tradeInfo WHERE remark LIKE ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, "%" + keyword + "%");
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(mapResultSetToTransaction(rs));
                }
            }
        }
        return list;
    }

    /**
     * 按日期范围查询交易记录
     */
    public List<Transaction> getTransactionsByDateRange(Date start, Date end) throws SQLException {
        List<Transaction> list = new ArrayList<>();
        String sql = "SELECT * FROM tradeInfo WHERE tradeDate BETWEEN ? AND ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setTimestamp(1, new Timestamp(start.getTime()));
            ps.setTimestamp(2, new Timestamp(end.getTime()));
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(mapResultSetToTransaction(rs));
                }
            }
        }
        return list;
    }

    /**
     * 按客户ID查询交易记录
     */
    public List<Transaction> getTransactionsByCustomerId(int customerId) throws SQLException {
        List<Transaction> transactions = new ArrayList<>();
        String sql = "SELECT t.* FROM tradeInfo t " +
                "JOIN cardInfo c ON t.cardID = c.cardID " +
                "WHERE c.customerID = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, customerId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    transactions.add(mapResultSetToTransaction(rs));
                }
            }
        }
        return transactions;
    }

    /**
     * 组合查询交易记录（卡号或备注）
     */
    public List<Transaction> queryTransactions(String keyword) throws SQLException {
        List<Transaction> list = new ArrayList<>();
        String sql = "SELECT * FROM tradeInfo WHERE cardID LIKE ? OR remark LIKE ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, "%" + keyword + "%");
            ps.setString(2, "%" + keyword + "%");
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(mapResultSetToTransaction(rs));
                }
            }
        }
        return list;
    }

    /**
     * 获取最近N笔交易记录
     */
    public List<Transaction> getRecentTransactions(int limit) throws SQLException {
        List<Transaction> transactions = new ArrayList<>();
        String sql = "SELECT * FROM tradeInfo ORDER BY tradeDate DESC LIMIT ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, limit);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    transactions.add(mapResultSetToTransaction(rs));
                }
            }
        }
        return transactions;
    }

    /**
     * 统一结果集封装方法
     */
    private Transaction mapResultSetToTransaction(ResultSet rs) throws SQLException {
        Transaction transaction = new Transaction();
        transaction.setTradeID(rs.getInt("tradeID"));
        transaction.setTradeDate(rs.getTimestamp("tradeDate"));
        transaction.setTradeType(rs.getString("tradeType"));
        transaction.setCardID(rs.getString("cardID"));
        transaction.setTradeMoney(rs.getDouble("tradeMoney"));
        transaction.setRemark(rs.getString("remark"));
        return transaction;
    }

    public boolean updateTransaction(int tradeID, double newTradeMoney, String newRemark) throws SQLException {
        Connection conn = null;
        try {
            conn = DBUtil.getConnection();
            conn.setAutoCommit(false); // 开始事务

            // 1. 获取原始交易记录
            String selectSql = "SELECT * FROM tradeInfo WHERE tradeID = ? FOR UPDATE";
            Transaction originalTransaction = null;
            try (PreparedStatement selectPs = conn.prepareStatement(selectSql)) {
                selectPs.setInt(1, tradeID);
                try (ResultSet rs = selectPs.executeQuery()) {
                    if (rs.next()) {
                        originalTransaction = mapResultSetToTransaction(rs);
                    } else {
                        throw new SQLException("交易记录不存在");
                    }
                }
            }

            // 2. 计算金额差异
            double amountDifference = newTradeMoney - originalTransaction.getTradeMoney();

            // 3. 根据交易类型调整银行卡余额
            String tradeType = originalTransaction.getTradeType();
            String updateCardSql = "";

            if ("存款".equals(tradeType) || "存入".equals(tradeType)) {
                // 存款类交易：增加金额会减少余额，减少金额会增加余额
                updateCardSql = "UPDATE cardInfo SET balance = balance + ? WHERE cardID = ?";
                amountDifference = -amountDifference; // 反向调整
            } else if ("取款".equals(tradeType) || "支取".equals(tradeType)) {
                // 取款类交易：增加金额会增加余额，减少金额会减少余额
                updateCardSql = "UPDATE cardInfo SET balance = balance - ? WHERE cardID = ?";
            } else {
                // 转账等系统交易不允许修改金额
                throw new SQLException("系统交易记录（如转账）不能修改金额");
            }

            // 4. 更新银行卡余额
            try (PreparedStatement updateCardPs = conn.prepareStatement(updateCardSql)) {
                updateCardPs.setDouble(1, Math.abs(amountDifference));
                updateCardPs.setString(2, originalTransaction.getCardID());
                int rows = updateCardPs.executeUpdate();
                if (rows == 0) {
                    throw new SQLException("更新银行卡余额失败");
                }
            }

            // 5. 更新交易记录
            String updateSql = "UPDATE tradeInfo SET tradeMoney = ?, remark = ? WHERE tradeID = ?";
            try (PreparedStatement updatePs = conn.prepareStatement(updateSql)) {
                updatePs.setDouble(1, newTradeMoney);
                updatePs.setString(2, newRemark);
                updatePs.setInt(3, tradeID);
                updatePs.executeUpdate();
            }

            conn.commit(); // 提交事务
            return true;
        } catch (SQLException e) {
            if (conn != null) {
                conn.rollback(); // 回滚事务
            }
            throw e;
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(true); // 恢复自动提交
                } finally {
                    conn.close();
                }
            }
        }
    }
}